<?php
namespace app\controllers\crontab;
use Yii;
use app\models\marketing\MailGroup;
use app\models\marketing\OrderInfo;
use app\models\marketing\GroupConversionStatistics;

class DealGroupConversionStatisticsController extends \app\base\Controller 
{
    
    /**
     * 初始化
     */
    public function init()
    {
//        yii::$app->db->enableSlaves = false;        // 禁用从库查询，从主库查询数据。
    }
    
     /**
     * yumao
     * 2018-3-13
     * 验证过滤
     */
    private function actionVerify($siteId)
    {
        if (empty($siteId)) {
            echo '必须传递网站id';
            exit;
        }
         // 判断当前网站是否存在
        $siteInfo = Yii::$app->params['sites']['marketing_email']['web_index'][$siteId];
        
        if (empty($siteInfo)) {
            echo '当前网站不存在';
            exit;
        }
    }
    
    /**
     * 计算下单率和下单转化率
     * yumao
     * 2018-4-8
     */
    public function actionCalculateLv()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $groupConversionStatistics = new GroupConversionStatistics();
        $groupConversionStatistics->calculateLv();
        echo '下单率和下单转化率统计完毕';
    }
    /**
     * 把订单相关数据统计到mail_group_conversion_statistics表中
     * yumao
     * 2018-4-8
     */
    public function actionStatisticsOrderData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $mothod = strtolower(Yii::$app->request->getMethod());
        $siteId = Yii::$app->request->$mothod('site_id');
        $this->actionVerify($siteId);
        
        // 获取没有处理的
        $orderInfo = new OrderInfo();
        $utmsourceValueArr = $orderInfo->getUnStatisticsOrderUtmsource($siteId);
        
        if (!empty($utmsourceValueArr)) {
            // 遍历处理相关数据
            $utmsourceArrDeal = [];
            $idArr = [];
//        echo '<pre>';
//        var_dump($utmsourceValueArr);
//        exit;
//        foreach ($utmsourceValueArr as $utmsourceValueArrVal) {
//            $matchVal = [];
//            $utmsourceTemp = [];
//            preg_match('/.*?\d{10}/is', $utmsourceValueArrVal['value3'],$matchVal);
//            // if (!empty($matchVal) && !empty($matchVal[0]) && !in_array($matchVal[0], $utmsourceArrDeal)) {
//            if (!empty($matchVal) && !empty($matchVal[0])) {
//                $utmsourceTemp['deal'] = $matchVal[0];
//                $utmsourceTemp['undeal'] = $utmsourceValueArrVal['value3'];
//                $utmsourceArrDeal[] = $utmsourceTemp;
//            } 
//        }
            foreach ($utmsourceValueArr as $utmsourceValueArrVal) {
                if (!in_array($utmsourceValueArrVal['value4'], $utmsourceArrDeal)) { // 去重
                    $utmsourceArrDeal[] = $utmsourceValueArrVal['value4'];
                }
                $idArr[] = $utmsourceValueArrVal['id'];
            }

            // 查询这些分组的下单用户数和订单金额
            $orderUserNumArr = $orderInfo->getOrderUserNum($utmsourceArrDeal,$siteId);
            
            // 查询这些分组的订单数
            $orderNumArr = $orderInfo->getOrderNum($utmsourceArrDeal,$siteId);
            
            // 具体订单金额
            $orderAmountArr = $orderInfo->getOrderAmount($utmsourceArrDeal,$siteId);
           
            // 查出具体分组id值
            $mailGroup = new MailGroup();
            $groupInfoArr = $mailGroup->getGroupIdByUtmsource($utmsourceArrDeal,$siteId);
            // 处理数据
            $orderUserNumArrDeal = [];
            $orderNumArrDeal = [];
            $orderAmountArrDeal = [];
            $groupInfoArrDeal = [];
            foreach ($orderUserNumArr as $orderUserNumArrVal) {
                $orderUserNumArrDeal[$orderUserNumArrVal['value4']] = $orderUserNumArrVal;
            }
            foreach ($orderNumArr as $orderNumArrVal) {
                $orderNumArrDeal[$orderNumArrVal['value4']] = $orderNumArrVal;
            }
            foreach ($orderAmountArr as $orderAmountArrVal) {
                $orderAmountArrDeal[$orderAmountArrVal['value4']] = $orderAmountArrVal;
            }
            
            foreach ($groupInfoArr as $groupInfoArrVal) {
                $matchVal = [];
                preg_match('/.*?\d{10}/is', $groupInfoArrVal['group_name'],$matchVal);
                if (!empty($matchVal) && !empty($matchVal[0])) {
                    $groupInfoArrDeal[$matchVal[0]] = $groupInfoArrVal;
                }
            }
            
            // 数据更新到统计表
            $groupConversionStatistics = new GroupConversionStatistics();
            $groupConversionStatistics->updateOrderInfo($groupInfoArrDeal,$orderUserNumArrDeal,$orderNumArrDeal,$orderAmountArrDeal,$siteId);
           
            // 更新vw_order_mail_info表中这些数据的状态
            $orderInfo->updateStatistics($idArr,$siteId);
            echo "数据处理完毕";
        }
    }
    
    /*
     * 把服装站系统订单相关数据统计到mail_group_conversion_statistics和mail_platform_order_statistics表中
     * liugang 
     * 2018-5-5
     */
    public function actionNewStatisticsOrderData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $gets = Yii::$app->request->get();
        
        // 统计前12小时的数据
        $endTime = isset($gets['end_date']) ? \yii::$app->bjTime->strtotime($gets['end_date']." 23:59:59") : \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d H").":00:00");
        $startTime = isset($gets['start_date']) ? \yii::$app->bjTime->strtotime($gets['start_date']) : \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",($endTime-2*3600))." 00:00:00");
        
        // 将订单数据统计到邮件转化表
       $orderInfo = new OrderInfo();
       $siteToWid = $orderInfo->getSiteToWid();
        
       foreach($siteToWid as $siteId => $wid) {
           
      
                $getDataSql = "select t.add_date,v.value4,v.terminal,count(*) order_nums,count(DISTINCT t.user_id) order_user_nums,sum(t.order_amount) order_money from vw_order_mail_info v join tb_eload_order_info t on v.order_sn = t.order_sn  where v.group_id = 0 and t.add_time >= $startTime and t.add_time <= $endTime and v.wid = $wid and v.value4 != '' group by t.add_date,v.value4,v.terminal";
                $sysOrderInfoArr = Yii::$app->db->createCommand($getDataSql)->queryAll();
                
                // 获取服装站系统邮件订单已完成的数据
                $getDataSql = "select t.add_date,v.value4,v.terminal,count(*) order_nums,sum(t.order_amount) order_money from vw_order_mail_info v join tb_eload_order_info t on v.order_sn = t.order_sn  where v.group_id = 0 and t.add_time >= $startTime and t.add_time <= $endTime and v.wid = $wid and v.value4 != '' and t.order_status = 1 group by t.add_date,v.value4,v.terminal";
                $sysPayedOrderInfoArr = Yii::$app->db->createCommand($getDataSql)->queryAll();
                
                $updatePlatData = $updatePlatDetailData = [];
        //        var_dump($sysOrderInfoArr);
                foreach($sysOrderInfoArr as $sysOrderInfo) {

                    if(!isset($updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_nums'])) {
                        $updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_nums'] = $sysOrderInfo['order_nums'];
                    } else {
                        $updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_nums'] += $sysOrderInfo['order_nums'];
                    }

                    if(!isset($updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_user_nums'])) {
                        $updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_user_nums'] = $sysOrderInfo['order_user_nums'];
                    } else {
                        $updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_user_nums'] += $sysOrderInfo['order_user_nums'];
                    }

                    if(!isset($updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_money'])) {
                        $updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_money'] = $sysOrderInfo['order_money'];
                    } else {
                        $updatePlatData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']]['order_money'] += $sysOrderInfo['order_money'];
                    }
                    $updatePlatDetailData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']][$sysOrderInfo['terminal']]['order_nums'] = $sysOrderInfo['order_nums'];
                    $updatePlatDetailData[$sysOrderInfo['add_date']][$sysOrderInfo['value4']][$sysOrderInfo['terminal']]['order_money'] = $sysOrderInfo['order_money'];
                }
                
                foreach($sysPayedOrderInfoArr as $sysPayedOrderInfo) {
                    if(!isset($updatePlatData[$sysPayedOrderInfo['add_date']][$sysPayedOrderInfo['value4']]['payed_order_nums'])) {
                        $updatePlatData[$sysPayedOrderInfo['add_date']][$sysPayedOrderInfo['value4']]['payed_order_nums'] = $sysPayedOrderInfo['order_nums'];
                    } else {
                        $updatePlatData[$sysPayedOrderInfo['add_date']][$sysPayedOrderInfo['value4']]['payed_order_nums'] += $sysPayedOrderInfo['order_nums'];
                    }
                    
                    if(!isset($updatePlatData[$sysPayedOrderInfo['add_date']][$sysPayedOrderInfo['value4']]['payed_order_money'])) {
                        $updatePlatData[$sysPayedOrderInfo['add_date']][$sysPayedOrderInfo['value4']]['payed_order_money'] = $sysPayedOrderInfo['order_money'];
                    } else {
                        $updatePlatData[$sysPayedOrderInfo['add_date']][$sysPayedOrderInfo['value4']]['payed_order_money'] += $sysPayedOrderInfo['order_money'];
                    }
                }
                
                Yii::$app->db->enableSlaves = false;
                foreach($updatePlatData as $completed_date => $updateData) {
                    foreach($updateData as $utm_campaign => $data) {
                        $payedOrderNums = isset($data['payed_order_nums']) ? $data['payed_order_nums'] : 0;
                        $payedOrderMoney = isset($data['payed_order_money']) ? $data['payed_order_money'] : 0;
                        
                        $selectSql = 'select group_id from mail_group_conversion_statistics where template_name="'.$utm_campaign.'" and sys_mail_date ="'.$completed_date.'" and mail_business = 1 and site_id = '.$siteId;
                        if($conData = Yii::$app->db->createCommand($selectSql)->queryOne()) {
                            $curGroupId = $conData['group_id'];
                            Yii::$app->db->createCommand("update mail_group_conversion_statistics set order_nums = {$data['order_nums']},order_user_nums = {$data['order_user_nums']},order_money = {$data['order_money']},payed_order_nums={$payedOrderNums},payed_order_money={$payedOrderMoney} where template_name = '$utm_campaign' and sys_mail_date = '$completed_date' and mail_business = 1 and site_id = $siteId")->execute();
                        } else {
                            $minGroupId = Yii::$app->db->createCommand("select min(group_id) from mail_group_conversion_statistics where mail_business = 1 and group_id < 0")->queryScalar();
                            $curGroupId = $minGroupId ? $minGroupId - 1 : -1;
                            $insertSql = "insert into mail_group_conversion_statistics(group_id,site_id,template_name,mail_business,order_nums,order_user_nums,order_money,sys_mail_date,add_date,payed_order_nums,payed_order_money) values ".
                                    "($curGroupId,$siteId,'{$utm_campaign}',1,{$data['order_nums']},{$data['order_user_nums']},{$data['order_money']},'{$completed_date}',".yii::$app->bjTime->time().",{$payedOrderNums},{$payedOrderMoney}) ON DUPLICATE KEY UPDATE order_nums = {$data['order_nums']},order_user_nums=values(order_user_nums),order_money=values(order_money),payed_order_nums=values(payed_order_nums),payed_order_money=values(payed_order_money) ";

                            Yii::$app->db->createCommand($insertSql)->execute();
                    
                        }

                        if(isset($updatePlatDetailData[$completed_date][$utm_campaign])) {
                            $update = [];
                            $insertPlatSql = "insert into mail_platform_order_statistics(group_id,site_id,template_mark,platform,order_nums,order_amount) values";
                            foreach($updatePlatDetailData[$completed_date][$utm_campaign] as $platform => $platData) {
                                $update[] = "($curGroupId,$siteId,'{$utm_campaign}','{$platform}','{$platData['order_nums']}','{$platData['order_money']}')";
                            }
                            if($update) {
                                $insertPlatSql .= join(',',$update);
                                $insertPlatSql .= ' ON DUPLICATE KEY UPDATE order_nums = values(order_nums),order_amount=values(order_amount) ';
                                Yii::$app->db->createCommand($insertPlatSql)->execute();
                            }

                        }

                    }
                }
                
                
        }
        
        echo "数据处理完成！";
    }
    
    /*
     * 统计gb系统邮件下单数据到分组转化表
     * /crontab/deal-group-conversion-statistics/statistics-gb-order-data?site=marketing_email
     * @liugang 2018-5-2
     */
    public function actionStatisticsGbOrderData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $gets = Yii::$app->request->get();
        
        // 统计前12小时的数据
        $endTime = isset($gets['end_date']) ? \yii::$app->bjTime->strtotime($gets['end_date']." 23:59:59") : \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d H").":00:00");
        $startTime = isset($gets['start_date']) ? \yii::$app->bjTime->strtotime($gets['start_date']) : \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",($endTime-12*3600))." 00:00:00");
        
        
        $getDataSql = 'select created_date,pipeline_code,platform,utm_campaign,count(*) order_nums,count(DISTINCT user_id) order_user_nums,sum(order_amount) order_money  from gearbest_order_sys_data where created_time >= '.$startTime.' and created_time <= '.$endTime.' group by created_date,pipeline_code,platform,utm_campaign';
        $sysOrderInfoArr = Yii::$app->db->createCommand($getDataSql)->queryAll();
        
        // 获取服装站系统邮件订单已完成的数据
        $getDataSql = 'select created_date,pipeline_code,platform,utm_campaign,count(*) order_nums,sum(order_amount) order_money  from gearbest_order_sys_data where pay_status = 3 and created_time >= '.$startTime.' and created_time <= '.$endTime.' group by created_date,pipeline_code,platform,utm_campaign';
        $sysPayedOrderInfoArr = Yii::$app->db->createCommand($getDataSql)->queryAll();
        
        
        $updatePlatData = $updatePlatDetailData = [];
//        var_dump($sysOrderInfoArr);
        foreach($sysOrderInfoArr as $sysOrderInfo) {
            
            if(!isset($updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_nums'])) {
                $updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_nums'] = $sysOrderInfo['order_nums'];
            } else {
                $updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_nums'] += $sysOrderInfo['order_nums'];
            }
            
            if(!isset($updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_user_nums'])) {
                $updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_user_nums'] = $sysOrderInfo['order_user_nums'];
            } else {
                $updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_user_nums'] += $sysOrderInfo['order_user_nums'];
            }
            
            if(!isset($updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_money'])) {
                $updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_money'] = $sysOrderInfo['order_money'];
            } else {
                $updatePlatData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']]['order_money'] += $sysOrderInfo['order_money'];
            }
            $updatePlatDetailData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']][$sysOrderInfo['platform']]['order_nums'] = $sysOrderInfo['order_nums'];
            $updatePlatDetailData[$sysOrderInfo['created_date']][$sysOrderInfo['pipeline_code']][$sysOrderInfo['utm_campaign']][$sysOrderInfo['platform']]['order_money'] = $sysOrderInfo['order_money'];
        }
        
        foreach($sysPayedOrderInfoArr as $sysPayedOrderInfo) {
            if(!isset($updatePlatData[$sysPayedOrderInfo['created_date']][$sysPayedOrderInfo['pipeline_code']][$sysPayedOrderInfo['utm_campaign']]['payed_order_nums'])) {
                $updatePlatData[$sysPayedOrderInfo['created_date']][$sysPayedOrderInfo['pipeline_code']][$sysPayedOrderInfo['utm_campaign']]['payed_order_nums'] = $sysPayedOrderInfo['order_nums'];
            } else {
                $updatePlatData[$sysPayedOrderInfo['created_date']][$sysPayedOrderInfo['pipeline_code']][$sysPayedOrderInfo['utm_campaign']]['payed_order_nums'] += $sysPayedOrderInfo['order_nums'];
            }
            
            if(!isset($updatePlatData[$sysPayedOrderInfo['created_date']][$sysPayedOrderInfo['pipeline_code']][$sysPayedOrderInfo['utm_campaign']]['payed_order_money'])) {
                $updatePlatData[$sysPayedOrderInfo['created_date']][$sysPayedOrderInfo['pipeline_code']][$sysPayedOrderInfo['utm_campaign']]['payed_order_money'] = $sysPayedOrderInfo['order_money'];
            } else {
                $updatePlatData[$sysPayedOrderInfo['created_date']][$sysPayedOrderInfo['pipeline_code']][$sysPayedOrderInfo['utm_campaign']]['payed_order_money'] += $sysPayedOrderInfo['order_money'];
            }
        }
        
        Yii::$app->db->enableSlaves = false;
        
        foreach($updatePlatData as $completed_date => $dealData) {
            foreach($dealData as $pipelineCode => $updateData) {
                foreach($updateData as $utm_campaign => $data) {
                    $payedOrderNums = isset($data['payed_order_nums']) ? $data['payed_order_nums'] : 0;
                    $payedOrderMoney = isset($data['payed_order_money']) ? $data['payed_order_money'] : 0;

                    $selectSql = 'select group_id from mail_group_conversion_statistics where template_name="'.$utm_campaign.'" and sys_mail_date ="'.$completed_date.'" and pipeline_code = "'.$pipelineCode.'" and mail_business = 1 and site_id = 12';
                    if($conData = Yii::$app->db->createCommand($selectSql)->queryOne()) {
                        $curGroupId = $conData['group_id'];
                        Yii::$app->db->createCommand("update mail_group_conversion_statistics set order_nums = {$data['order_nums']},order_user_nums = {$data['order_user_nums']},order_money = {$data['order_money']},payed_order_nums={$payedOrderNums},payed_order_money={$payedOrderMoney} where template_name = '$utm_campaign' and sys_mail_date = '$completed_date' and pipeline_code = '$pipelineCode' and mail_business = 1 and site_id = 12")->execute();
                    } else {
                        $minGroupId = Yii::$app->db->createCommand("select min(group_id) from mail_group_conversion_statistics where mail_business = 1 and group_id < 0")->queryScalar();
                        $curGroupId = $minGroupId ? $minGroupId - 1 : -1;
                        $insertSql = "insert into mail_group_conversion_statistics(group_id,site_id,template_name,pipeline_code,mail_business,order_nums,order_user_nums,order_money,sys_mail_date,add_date,payed_order_nums,payed_order_money) values ".
                                "($curGroupId,12,'{$utm_campaign}','{$pipelineCode}',1,{$data['order_nums']},{$data['order_user_nums']},{$data['order_money']},'{$completed_date}',".yii::$app->bjTime->time().",{$payedOrderNums},{$payedOrderMoney}) ON DUPLICATE KEY UPDATE order_nums = {$data['order_nums']},order_user_nums=values(order_user_nums),order_money=values(order_money),payed_order_nums=values(payed_order_nums),payed_order_money=values(payed_order_money),pipeline_code=values(pipeline_code) ";

                        Yii::$app->db->createCommand($insertSql)->execute();

                    }

                    if(isset($updatePlatDetailData[$completed_date][$pipelineCode][$utm_campaign])) {
                        $update = [];
                        $insertPlatSql = "insert into mail_platform_order_statistics(group_id,site_id,template_mark,platform,order_nums,order_amount) values";
                        foreach($updatePlatDetailData[$completed_date][$pipelineCode][$utm_campaign] as $platform => $platData) {
                            $update[] = "($curGroupId,12,'{$utm_campaign}','{$platform}','{$platData['order_nums']}','{$platData['order_money']}')";
                        }
                        if($update) {
                            $insertPlatSql .= join(',',$update);
                            $insertPlatSql .= ' ON DUPLICATE KEY UPDATE order_nums = values(order_nums),order_amount=values(order_amount) ';
                            Yii::$app->db->createCommand($insertPlatSql)->execute();
                        }

                    }

                }
            }
        }
        
        echo "数据处理完成！";
    }
    
    
    /*
     * 统计邮件转化数据到表中
     * @liugang 2018-2-6
     * 
     */
    public function actionStatisticsData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $limit = 2000; // 每次处理的数据量
        $offset = 0; // 从什么位置开始去数据
        
        // 将分组表数据写入到转化统计表中
        $insertSql = "insert into mail_group_conversion_statistics(group_id,site_id,channel_id,template_id,send_ok_count,open_count,click_count,add_date) (select group_id,site_id,channel_id,template_id,send_ok_count,open_count,click_link_count,add_date from mail_group where is_del = 0 and send_ok_count > 0 and is_inside_test = 0 and is_ab_test = 0)  ON DUPLICATE KEY UPDATE send_ok_count=values(send_ok_count),open_count=values(open_count),click_count=values(click_count) ";
        Yii::$app->db->createCommand($insertSql)->execute();
        
        // 将分组表数据写入到转化统计表中
        $insertSql = "insert into mail_group_conversion_statistics(group_id,site_id,channel_id,template_id,send_ok_count,open_count,click_count,add_date) (select g.group_id,g.site_id,g.channel_id,m.template_id,g.send_ok_count,g.open_count,g.click_link_count,g.add_date from mail_group g join mail_template m on g.template_id = m.id where g.is_del = 0 and g.send_ok_count > 0 and g.is_inside_test = 0 and g.is_ab_test = 1 and g.ab_test_parent_id > 0)  ON DUPLICATE KEY UPDATE send_ok_count=values(send_ok_count),open_count=values(open_count),click_count=values(click_count) ";
        Yii::$app->db->createCommand($insertSql)->execute();
        
        // 更新mail_group_email_type_statistics表的站点id和模板模板名称
        $updateGroupEmailTypeSql = "update mail_group_email_type_statistics s join mail_group g on s.gid = g.group_id set s.site_id = g.site_id,s.template_id = g.template_id where (s.site_id = 0 or s.template_id = 0) and g.is_ab_test = 0";
        Yii::$app->db->createCommand($updateGroupEmailTypeSql)->execute();
        
        // 更新mail_group_email_type_statistics表的站点id和模板模板名称
        $updateGroupEmailTypeSql = "update mail_group_email_type_statistics s join mail_group g on s.gid = g.group_id join mail_template m on g.template_id = m.id set s.site_id = g.site_id,s.template_id = m.template_id where (s.site_id = 0 or s.template_id = 0) and g.is_ab_test = 1";
        Yii::$app->db->createCommand($updateGroupEmailTypeSql)->execute();
        
        $updateGroupSuccessNumsSql = "update mail_group_email_type_statistics s set s.real_send_success = (cast(s.sent as signed) - cast(s.send_fail as signed))";
        Yii::$app->db->createCommand($updateGroupSuccessNumsSql)->execute();
        
        // 休眠5秒，确保数据插入完成后再进行后面的操作
        sleep(5);
        
        while(1) {
            // 从转化统计表批量取出数据处理
            $selectSql = "select c.site_id,c.group_id,c.template_id,c.send_ok_count,g.group_name from mail_group_conversion_statistics c join mail_group g on c.group_id = g.group_id where c.group_id > 0 order by c.id limit $offset,$limit";
            
            $groupConversionInfoArr = Yii::$app->db->createCommand($selectSql)->queryAll();
            
            // 如果没有查出来数据，说明数据处理完了，退出循环
            if(!$groupConversionInfoArr) {
                break;
            }
            // 变量初始化
            $groupIdsArr = $templateIdsArr = $updateData = $sendOkCountArr = $groupNameArr = [];
            
            foreach($groupConversionInfoArr as $groupConversionInfo) {
                $groupIdsArr[] = $groupConversionInfo['group_id'];
                
                if($groupConversionInfo['site_id'] == 12) {
                    
                    preg_match('/.*?\d{10}/is', $groupConversionInfo['group_name'],$matchVal);
                    if (!empty($matchVal) && !empty($matchVal[0])) {
                        $groupNameArr[$groupConversionInfo['group_id']] = "'".$matchVal[0]."'";
                    }
                    
                }
                
                $templateIdsArr[$groupConversionInfo['group_id']] = $groupConversionInfo['template_id'];
                $sendOkCountArr[$groupConversionInfo['group_id']] = $groupConversionInfo['send_ok_count'];
            }
            
            // 获取gb对应分组的下单数据
            if($groupNameArr) {
                $getOrderSql = 'select deal_utm_campaign,count(*) order_nums,count(DISTINCT user_id) order_user_nums,sum(order_amount) order_money from gearbest_order_market_data where deal_utm_campaign in ('.join(',', $groupNameArr).') group by deal_utm_campaign';
                $orderInfoArr = Yii::$app->db->createCommand($getOrderSql)->queryAll();
                foreach($orderInfoArr as $orderInfo) {
                    
                    $group_id = array_search("'".$orderInfo['deal_utm_campaign']."'", $groupNameArr);
                    if($group_id) {
                        $updateData[$group_id]['order_nums'] = $orderInfo['order_nums'];
                        $updateData[$group_id]['order_user_nums'] = $orderInfo['order_user_nums'];
                        $updateData[$group_id]['order_money'] = $orderInfo['order_money'];
                    }
                }
                
                // 获取gb对应分组已完成支付的订单数据
                $getOrderSql = 'select deal_utm_campaign,count(*) order_nums,sum(order_amount) order_money from gearbest_order_market_data where deal_utm_campaign in ('.join(',', $groupNameArr).') and pay_status = 3 group by deal_utm_campaign';
                $orderInfoArr = Yii::$app->db->createCommand($getOrderSql)->queryAll();
                foreach($orderInfoArr as $orderInfo) {
                    
                    $group_id = array_search("'".$orderInfo['deal_utm_campaign']."'", $groupNameArr);
                    if($group_id) {
                        $updateData[$group_id]['payed_order_nums'] = $orderInfo['order_nums'];
                        $updateData[$group_id]['payed_order_money'] = $orderInfo['order_money'];
                    }
                }
                
                // 按平台类型获取对应分组订单数据
                $getOrderByPlatSql = 'select deal_utm_campaign,platform,count(*) order_nums,sum(order_amount) order_money from gearbest_order_market_data where deal_utm_campaign in ('.join(',', $groupNameArr).') group by deal_utm_campaign,platform';
                $orderInfoByPlatArr = Yii::$app->db->createCommand($getOrderByPlatSql)->queryAll();
                
                $insertOrderSql = 'insert into mail_platform_order_statistics(group_id,site_id,template_mark,platform,order_nums,order_amount) values';
                $insertOrderArr = [];
                foreach($orderInfoByPlatArr as $orderInfoByPlat) {
                    
                    $group_id = array_search("'".$orderInfoByPlat['deal_utm_campaign']."'", $groupNameArr);
                    if($group_id) {
                        $template_id = isset($templateIdsArr[$group_id]) ? $templateIdsArr[$group_id] : 0;
                        $insertOrderArr[] = "($group_id,12,{$template_id},'{$orderInfoByPlat['platform']}','{$orderInfoByPlat['order_nums']}','{$orderInfoByPlat['order_money']}')";
                    }
                }
                
                if($insertOrderArr) {
                    $insertOrderSql .= join(',',$insertOrderArr);
                    $insertOrderSql .= ' ON DUPLICATE KEY UPDATE order_nums = values(order_nums),order_amount=values(order_amount) ';
                    Yii::$app->db->createCommand($insertOrderSql)->execute();
                }
                
            }
            
            // 处理下单数据
            $getMarketSql = "select v.site_id,v.group_id,v.terminal,count(*) order_nums,count(DISTINCT t.user_id) order_user_nums,sum(t.order_amount) order_money from vw_order_mail_info v join tb_eload_order_info t on v.order_sn = t.order_sn  where v.group_id > 0 and v.group_id in(".join(',',$groupIdsArr).") group by v.group_id,v.terminal";
            $marketOrderInfoArr = Yii::$app->db->createCommand($getMarketSql)->queryAll();
            $insertTerminalArr = [];
            $insertTerminalSql = 'insert into mail_platform_order_statistics(group_id,site_id,template_mark,platform,order_nums,order_amount) values';
            
            foreach($marketOrderInfoArr as $marketOrderInfo) {
                if($marketOrderInfo['group_id']) {
                    $updateData[$marketOrderInfo['group_id']]['order_nums'] = isset($updateData[$marketOrderInfo['group_id']]['order_nums']) ? $updateData[$marketOrderInfo['group_id']]['order_nums']+$marketOrderInfo['order_nums'] : $marketOrderInfo['order_nums'];
                    $updateData[$marketOrderInfo['group_id']]['order_user_nums'] = isset($updateData[$marketOrderInfo['group_id']]['order_user_nums']) ? $updateData[$marketOrderInfo['group_id']]['order_user_nums'] + $marketOrderInfo['order_user_nums'] : $marketOrderInfo['order_user_nums'];
                    $updateData[$marketOrderInfo['group_id']]['order_money'] = isset($updateData[$marketOrderInfo['group_id']]['order_money']) ? $updateData[$marketOrderInfo['group_id']]['order_money'] + $marketOrderInfo['order_money'] : $marketOrderInfo['order_money'];
                    
                    $template_id = isset($templateIdsArr[$marketOrderInfo['group_id']]) ? $templateIdsArr[$marketOrderInfo['group_id']] : 0;
                    $insertTerminalArr[] = "({$marketOrderInfo['group_id']},{$marketOrderInfo['site_id']},{$template_id},'{$marketOrderInfo['terminal']}','{$marketOrderInfo['order_nums']}','{$marketOrderInfo['order_money']}')";
                }
                
            }
            
            if($insertTerminalArr) {
                $insertTerminalSql .= join(',',$insertTerminalArr);
                $insertTerminalSql .= ' ON DUPLICATE KEY UPDATE order_nums = values(order_nums),order_amount=values(order_amount) ';
                Yii::$app->db->createCommand($insertTerminalSql)->execute();
            }
            
            // 获取服装站已支付的订单数据
            $getMarketSql = "select v.site_id,v.group_id,v.terminal,count(*) order_nums,sum(t.order_amount) order_money from vw_order_mail_info v join tb_eload_order_info t on v.order_sn = t.order_sn  where v.group_id > 0 and v.group_id in(".join(',',$groupIdsArr).") and t.order_status = 1 group by v.group_id,v.terminal";
            $marketOrderInfoArr = Yii::$app->db->createCommand($getMarketSql)->queryAll();
            
            foreach($marketOrderInfoArr as $marketOrderInfo) {
                if($marketOrderInfo['group_id']) {
                    $updateData[$marketOrderInfo['group_id']]['payed_order_nums'] = isset($updateData[$marketOrderInfo['group_id']]['payed_order_nums']) ? $updateData[$marketOrderInfo['group_id']]['payed_order_nums']+$marketOrderInfo['order_nums'] : $marketOrderInfo['order_nums'];
                    $updateData[$marketOrderInfo['group_id']]['payed_order_money'] = isset($updateData[$marketOrderInfo['group_id']]['payed_order_money']) ? $updateData[$marketOrderInfo['group_id']]['payed_order_money'] + $marketOrderInfo['order_money'] : $marketOrderInfo['order_money'];
                    
                }
                
            }
            // 获取服装站已支付的订单数据结束
            // 处理下单数据结束
            
            if($templateIdsArr) {
                // 根据模板id获取模板名称及类型
                $getTemplateSql = 'select m.template_id,m.template_name,m.is_del,t.id as template_type_id from mail_template m join template_type t on m.type_id = t.id where m.template_id in ('.implode(',',$templateIdsArr).')  and m.is_use = 1 and t.state = 1 and m.is_ab_test = 0';
                
                $templateInfoArr = Yii::$app->db->createCommand($getTemplateSql)->queryAll();
                
                if($templateInfoArr) {
                    foreach($templateInfoArr as $templateInfo) {
                        foreach($templateIdsArr as $group_id => $template_id) {
                            if($templateInfo['is_del'] == 0 && $templateInfo['template_id'] == $template_id) {
                                $updateData[$group_id]['template_name'] = $templateInfo['template_name'];
                                $updateData[$group_id]['template_type_id'] = $templateInfo['template_type_id'];
                            
                            } elseif($templateInfo['template_id'] == $template_id) {
                                $updateData[$group_id]['template_name'] = $templateInfo['template_name'];
                                $updateData[$group_id]['template_type_id'] = $templateInfo['template_type_id'];
                            }
                        }
                        
                    }
                }
                
            }
            
            // 从channel_return_info_statistics中获取需要的分组数据
            if($groupIdsArr) {
                
                $getGroupSql = 'select sum(send_success_nums) send_success_nums,sum(complaint_nums) complaint_nums,sum(unsubscribe_nums) unsubscribe_nums,sum(hard_bounces_nums) hard_bounces_nums,sum(soft_bounces_nums) soft_bounces_nums,gid,channel_id from channel_return_info_statistics where gid in ('.implode(',',$groupIdsArr).') group by gid';
                
                $groupsInfoArr = Yii::$app->db->createCommand($getGroupSql)->queryAll();
                
                if($groupsInfoArr) {
                    
                    foreach($groupsInfoArr as $groupsInfo) {
                        
                        if($groupsInfo['channel_id'] == 2 || $groupsInfo['channel_id'] == 6 || $groupsInfo['channel_id'] == 5 || $groupsInfo['channel_id'] == 9) {
                            $updateData[$groupsInfo['gid']]['send_success_nums'] = $sendOkCountArr[$groupsInfo['gid']] ? ($sendOkCountArr[$groupsInfo['gid']] - $groupsInfo['hard_bounces_nums'] - $groupsInfo['soft_bounces_nums']) : 0;
                        } else {
                            $updateData[$groupsInfo['gid']]['send_success_nums'] = $groupsInfo['send_success_nums'];
                        }
                        
                        $updateData[$groupsInfo['gid']]['complaint_nums'] = $groupsInfo['complaint_nums'];
                        $updateData[$groupsInfo['gid']]['unsubscribe_nums'] = $groupsInfo['unsubscribe_nums'];
                        $updateData[$groupsInfo['gid']]['hard_bounces_nums'] = $groupsInfo['hard_bounces_nums'];
                        $updateData[$groupsInfo['gid']]['soft_bounces_nums'] = $groupsInfo['soft_bounces_nums'];
                    
                    }
                }
                
            }
            
            $updateGroupTemplateNameArr = $updateGroupTemplateTypeIdArr = $updateGroupSuccessNumsArr = $updateGroupComplaintNumsArr = $updateGroupUnsubscribeNumsArr = $updateGroupHardBouncesNumsArr = $updateGroupSoftBouncesNumsArr = $updateGroupArr = $updateGroupOrderNumsArr = $updateGroupOrderUserNumsArr = $updateGroupOrderMoneyArr = $updateGroupPayedOrderNumsArr = $updateGroupPayedOrderMoneyArr = [];
            // 将数据更新到转化统计表中
            if($updateData) {
                 $updateSql = "update mail_group_conversion_statistics set ";
                 
                 foreach($updateData as $groupId => $group) {
                     if(isset($group['template_name'])) {
                        $updateGroupTemplateNameArr[] = 'WHEN '.$groupId.' THEN "'.$group["template_name"].'" ';
                        $updateGroupTemplateTypeIdArr[] = "WHEN {$groupId} THEN {$group['template_type_id']} ";
                     }
                     if(isset($group['send_success_nums'])) {
                        $updateGroupSuccessNumsArr[] = "WHEN {$groupId} THEN {$group['send_success_nums']} ";
                        $updateGroupComplaintNumsArr[] = "WHEN {$groupId} THEN {$group['complaint_nums']} ";
                        $updateGroupUnsubscribeNumsArr[] = "WHEN {$groupId} THEN {$group['unsubscribe_nums']} ";
                        $updateGroupHardBouncesNumsArr[] = "WHEN {$groupId} THEN {$group['hard_bounces_nums']} ";
                        $updateGroupSoftBouncesNumsArr[] = "WHEN {$groupId} THEN {$group['soft_bounces_nums']} ";
                     }
                     if(isset($group['order_nums'])) {
                         $updateGroupOrderNumsArr[] = "WHEN {$groupId} THEN {$group['order_nums']} ";
                         $updateGroupOrderUserNumsArr[] = "WHEN {$groupId} THEN {$group['order_user_nums']} ";
                         $updateGroupOrderMoneyArr[] = "WHEN {$groupId} THEN {$group['order_money']} ";
                     }
                     if(isset($group['payed_order_nums'])) {
                         $updateGroupPayedOrderNumsArr[] = "WHEN {$groupId} THEN {$group['payed_order_nums']} ";
                         $updateGroupPayedOrderMoneyArr[] = "WHEN {$groupId} THEN {$group['payed_order_money']} ";
                     }
                     
                 }
                 
                if($updateGroupTemplateNameArr) {
                    $updateGroupArr[] = "template_name = CASE group_id ".join('',$updateGroupTemplateNameArr)." END";
                }
                if($updateGroupTemplateTypeIdArr) {
                    $updateGroupArr[] = "template_type_id = CASE group_id ".join('',$updateGroupTemplateTypeIdArr)." END";
                }
                if($updateGroupSuccessNumsArr) {
                    $updateGroupArr[] = "send_success_nums = CASE group_id ".join('',$updateGroupSuccessNumsArr)." END";
                }
                if($updateGroupComplaintNumsArr) {
                    $updateGroupArr[] = "complaint_nums = CASE group_id ".join('',$updateGroupComplaintNumsArr)." END";
                }
                if($updateGroupUnsubscribeNumsArr) {
                    $updateGroupArr[] = "unsubscribe_nums = CASE group_id ".join('',$updateGroupUnsubscribeNumsArr)." END";
                }
                if($updateGroupHardBouncesNumsArr) {
                    $updateGroupArr[] = "hard_bounces_nums = CASE group_id ".join('',$updateGroupHardBouncesNumsArr)." END";
                }
                if($updateGroupSoftBouncesNumsArr) {
                    $updateGroupArr[] = "soft_bounces_nums = CASE group_id ".join('',$updateGroupSoftBouncesNumsArr)." END";
                }
                if($updateGroupOrderNumsArr) {
                    $updateGroupArr[] = "order_nums = CASE group_id ".join('',$updateGroupOrderNumsArr)." END";
                }
                if($updateGroupOrderUserNumsArr) {
                    $updateGroupArr[] = "order_user_nums = CASE group_id ".join('',$updateGroupOrderUserNumsArr)." END";
                }
                if($updateGroupOrderMoneyArr) {
                    $updateGroupArr[] = "order_money = CASE group_id ".join('',$updateGroupOrderMoneyArr)." END";
                }
                
                if($updateGroupPayedOrderNumsArr) {
                    $updateGroupArr[] = "payed_order_nums = CASE group_id ".join('',$updateGroupPayedOrderNumsArr)." END";
                }
                
                if($updateGroupPayedOrderMoneyArr) {
                    $updateGroupArr[] = "payed_order_money = CASE group_id ".join('',$updateGroupPayedOrderMoneyArr)." END";
                }
                
                if($updateGroupArr) {
                    $updateSql .= join(',',$updateGroupArr)." where group_id in (".join(',',$groupIdsArr).")";
//                  var_dump($updateSql);
                    // 入库
                    Yii::$app->db->createCommand($updateSql)->execute();
                } 
                 
                 
            }
            
            
            // 更改取数位置
            $offset += $limit;
        }
        
        
        
        echo '邮件分组转化数据处理完成！';
        
    }
    
   
    /*
     * 记录相关日志
     */
    private function writeLog($msg)
    {
        $date = yii::$app->bjTime->date("Y-m-d H:i:s");
        $today = yii::$app->bjTime->date("Ymd");
        $dir = yii::$app->getRuntimePath() . "/logs/" . yii::$app->bjTime->date("Ym");
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
        }
        $filePath = $dir . "/do_channal_data_{$today}.log";
        $msg = "[{$date}] $msg" . PHP_EOL;
        file_put_contents($filePath, $msg, FILE_APPEND);
    }
}